<?php

include_once '../inc/db_util.php';

function viewByFilter($keyword) {
    $array_kuliah = array();
    $jsArray = json_decode($keyword, false);
	
    $query = mysql_query("SELECT * FROM tb_perkuliahan p 
        JOIN tb_mahasiswa m ON p.id_mahasiswa = m.id_mahasiswa
        JOIN tb_det_matkul dm ON p.id_det_matkul=dm.id_det_matkul
		JOIN tb_matkul_master mm ON mm.id_matkul = dm.id_matkul
        WHERE p.id_perkuliahan='" . $jsArray->{'Id'} . "'");

    while ($row = mysql_fetch_array($query, MYSQL_BOTH)) {
        $row_array['Id'] = $row['id_perkuliahan'];
        $row_array['UTS'] = $row['nilai_uts'];
        $row_array['UAS'] = $row['nilai_uas'];
        $row_array['Tugas'] = $row['nilai_tugas_tot'];
        $row_array['Quiz'] = $row['nilai_quis'];
        $row_array['Mhs']['Id'] = $row['id_mahasiswa'];
        $row_array['DetMatkul']['Id'] = $row['id_det_matkul'];
        $row_array['DetMatkul']['Matkul']['Id'] = $row['id_matkul'];
        $row_array['DetMatkul']['Matkul']['Kode'] = $row['kode_matkul'];
        $row_array['DetMatkul']['Matkul']['Nama'] = $row['matkul'];
        $row_array['DetMatkul']['Matkul']['SKS'] = $row['sks'];
        $row_array['DetMatkul']['Matkul']['Semester'] = $row['semester'];

        array_push($array_kuliah, $row_array);
    }
    return json_encode($array_kuliah);
}

function viewPerkuliahanDosen($filter) {
    $array_data = array();
    if (!is_null($filter)) {
        $jsArray = json_decode($filter, false);
        $query = mysql_query("SELECT * from tb_det_matkul dm
                        JOIN tb_matkul_master mm ON mm.id_matkul = dm.id_matkul
                        JOIN tb_dosen td ON dm.nip = td.nip  
                        WHERE td.nip ='" . $jsArray->{'Creator'}->{'Nip'} . "'
                        ORDER BY dm.id_det_matkul
                    ");
    } else {
        $query = mysql_query("SELECT * from tb_det_matkul dm
                        JOIN tb_matkul_master mm ON mm.id_matkul = dm.id_matkul
                        JOIN tb_dosen td ON dm.nip = td.nip
                        ORDER BY dm.id_det_matkul
                    ");
    }

    while ($row = mysql_fetch_array($query, MYSQL_BOTH)) {
        $row_array['Id'] = $row['id_det_matkul'];
        $row_array['TahunAjaran'] = $row['tahun_ajaran'];
        $row_array['Creator']['Nip'] = $row['nip'];
        $row_array['Creator']['Nama'] = $row['namadosen'];
        $row_array['Creator']['Username'] = $row['username'];
        $row_array['Creator']['Alamat'] = $row['alamat'];
        $row_array['Creator']['Telepon'] = $row['telepon'];
        $row_array['Creator']['Email'] = $row['email'];
        $row_array['Matkul']['Id'] = $row['id_matkul'];
        $row_array['Matkul']['Kode'] = $row['kode_matkul'];
        $row_array['Matkul']['Nama'] = $row['matkul'];
        $row_array['Matkul']['SKS'] = $row['sks'];
        $row_array['Matkul']['Semester'] = $row['semester'];

        array_push($array_data, $row_array);
    }
    return json_encode($array_data);
}

//list matakuliah yg belum diikuti
function viewPerkuliahanMhs($filter) {
    $array_data = array();
    $jsArray = json_decode($filter, false);
    $query = mysql_query("SELECT * FROM tb_det_matkul dm
                       JOIN tb_matkul_master mm ON mm.id_matkul = dm.id_matkul
                       JOIN tb_dosen td ON dm.nip = td.nip 
                       WHERE dm.id_det_matkul NOT IN 
                       (SELECT id_det_matkul FROM tb_perkuliahan WHERE id_mahasiswa='" . $jsArray->{'Id'} . "')
                       ORDER BY dm.id_det_matkul
                    ");


    while ($row = mysql_fetch_array($query, MYSQL_BOTH)) {
        $row_array['Id'] = $row['id_det_matkul'];
        $row_array['TahunAjaran'] = $row['tahun_ajaran'];
        $row_array['Creator']['Nip'] = $row['nip'];
        $row_array['Creator']['Nama'] = $row['namadosen'];
        $row_array['Creator']['Username'] = $row['username'];
        $row_array['Creator']['Alamat'] = $row['alamat'];
        $row_array['Creator']['Telepon'] = $row['telepon'];
        $row_array['Creator']['Email'] = $row['email'];
        $row_array['Matkul']['Id'] = $row['id_matkul'];
        $row_array['Matkul']['Kode'] = $row['kode_matkul'];
        $row_array['Matkul']['Nama'] = $row['matkul'];
        $row_array['Matkul']['SKS'] = $row['sks'];
        $row_array['Matkul']['Semester'] = $row['semester'];

        array_push($array_data, $row_array);
    }
    return json_encode($array_data);
}

//list matakuliah yg diikuti
function viewJoinedPerkuliahan($filter) {
    $array_data = array();
    $jsArray = json_decode($filter, false);
    $query = mysql_query("SELECT * FROM tb_perkuliahan tp
                       JOIN tb_det_matkul dm ON tp.id_det_matkul=dm.id_det_matkul
                       JOIN tb_matkul_master mm ON mm.id_matkul = dm.id_matkul
                       JOIN tb_dosen td ON dm.nip = td.nip 
                       WHERE tp.id_mahasiswa ='" . $jsArray->{'Id'} . "'
                       ORDER BY dm.id_det_matkul
                    ");

    while ($row = mysql_fetch_array($query, MYSQL_BOTH)) {
        $row_array['Id'] = $row['id_perkuliahan'];
        $row_array['UTS'] = $row['nilai_uts'];
        $row_array['UAS'] = $row['nilai_uas'];
        $row_array['Tugas'] = $row['nilai_tugas_tot'];
        $row_array['Quiz'] = $row['nilai_quis'];
        $row_array['DetMatkul']['Id'] = $row['id_det_matkul'];
        $row_array['DetMatkul']['TahunAjaran'] = $row['tahun_ajaran'];
        $row_array['DetMatkul']['Creator']['Nip'] = $row['nip'];
        $row_array['DetMatkul']['Creator']['Nama'] = $row['namadosen'];
        $row_array['DetMatkul']['Creator']['Username'] = $row['username'];
        $row_array['DetMatkul']['Creator']['Alamat'] = $row['alamat'];
        $row_array['DetMatkul']['Creator']['Telepon'] = $row['telepon'];
        $row_array['DetMatkul']['Creator']['Email'] = $row['email'];
        $row_array['DetMatkul']['Matkul']['Id'] = $row['id_matkul'];
        $row_array['DetMatkul']['Matkul']['Kode'] = $row['kode_matkul'];
        $row_array['DetMatkul']['Matkul']['Nama'] = $row['matkul'];
        $row_array['DetMatkul']['Matkul']['SKS'] = $row['sks'];
        $row_array['DetMatkul']['Matkul']['Semester'] = $row['semester'];

        array_push($array_data, $row_array);
    }
    return json_encode($array_data);
}

function findPerkuliahan($keyword) {
    $array_kuliah = array();
    $jsArray = json_decode($keyword, false);
    $query = mysql_query("SELECT * from tb_det_matkul dm
                      JOIN tb_matkul_master mm ON mm.id_matkul = dm.id_matkul
                      JOIN tb_dosen td ON dm.nip = td.nip 
                      WHERE td.nip ='" . $jsArray->{'Creator'}->{'Nip'} . "' AND
                      UPPER(mm.matkul) LIKE UPPER('%" . $jsArray->{'Matkul'}->{'Nama'} . "%')");

    while ($row = mysql_fetch_array($query, MYSQL_BOTH)) {
        $row_array['Id'] = $row['id_det_matkul'];
        $row_array['TahunAjaran'] = $row['tahun_ajaran'];
        $row_array['Creator']['Nip'] = $row['nip'];
        $row_array['Creator']['Nama'] = $row['namadosen'];
        $row_array['Creator']['Username'] = $row['username'];
        $row_array['Creator']['Alamat'] = $row['alamat'];
        $row_array['Creator']['Telepon'] = $row['telepon'];
        $row_array['Creator']['Email'] = $row['email'];
        $row_array['Matkul']['Id'] = $row['id_matkul'];
        $row_array['Matkul']['Kode'] = $row['kode_matkul'];
        $row_array['Matkul']['Nama'] = $row['matkul'];
        $row_array['Matkul']['SKS'] = $row['sks'];
        $row_array['Matkul']['Semester'] = $row['semester'];

        array_push($array_kuliah, $row_array);
    }
    return json_encode($array_kuliah);
}

function createPerkuliahan($json) {
    $jsArray = json_decode($json, false);

    $sql = "INSERT INTO tb_det_matkul(nip, tahun_ajaran, id_matkul, semester)
        VALUES (            
        '" . $jsArray->{'Creator'}->{'Nip'} . "',
        '" . $jsArray->{'TahunAjaran'} . "',
        '" . $jsArray->{'Matkul'}->{'Id'} . "',
        '" . $jsArray->{'Matkul'}->{'Semester'} . "'
        )";

    return mysql_query($sql);
}

function updatePerkuliahan($json) {
    $jsArray = json_decode($json, false);

    $sql = "UPDATE tb_det_matkul SET 
            id_matkul='" . $jsArray->{'Matkul'}->{'Id'} . "',
            semester='" . $jsArray->{'Matkul'}->{'Semester'} . "',
            tahun_ajaran='" . $jsArray->{'TahunAjaran'} . "'            
            WHERE id_det_matkul='" . $jsArray->{'Id'} . "'";

    return mysql_query($sql);
}

function leavePerkuliahan($json) {
    $sql = "DELETE FROM tb_perkuliahan 
        WHERE id_perkuliahan='" . $json->{'Id'} . "'";

    return mysql_query($sql);
}

function deletePerkuliahan($json) {
    $jsArray = json_decode($json, false);

    $sql = "DELETE FROM tb_det_matkul WHERE id_det_matkul='" . $jsArray->{'Id'} . "'";

    return mysql_query($sql);
}

function joinRequest($json) {
    $sql = "SELECT * FROM tb_temp_perkuliahan
        WHERE id_mahasiswa='" . $json->{'Mhs'}->{'Id'} . "'
        AND id_det_matkul='" . $json->{'DetMatkul'}->{'Id'} . "'";

    return mysql_num_rows(mysql_query($sql));
}

function alreadyJoined($json) {
    $sql = "SELECT * FROM tb_perkuliahan
        WHERE id_mahasiswa='" . $json->{'Mhs'}->{'Id'} . "'
        AND id_det_matkul='" . $json->{'DetMatkul'}->{'Id'} . "'";

    return mysql_num_rows(mysql_query($sql));
}

function joinKuliah($json) {
    $sql = "INSERT INTO tb_temp_perkuliahan(id_mahasiswa, id_det_matkul)
        VALUES (            
        '" . $json->{'Mhs'}->{'Id'} . "',
        '" . $json->{'DetMatkul'}->{'Id'} . "'
        )";

    return mysql_query($sql);
}

function viewJoinedMember($param) {
    $array_matkul = array();
    $jsArray = json_decode($param, false);
	
	$filter = "WHERE id_det_matkul='" . $jsArray->{'DetMatkul'}->{'Id'} . "'";
	if($jsArray->{'Mhs'} != null){
		$filter .= " AND (nim='" . $jsArray->{'Mhs'}->{'Nim'} . "'";
		$filter .= " OR nama like '%" . $jsArray->{'Mhs'}->{'Nama'} . "%')";
	}
    $query = mysql_query("SELECT * FROM tb_perkuliahan p 
			JOIN tb_mahasiswa m ON p.id_mahasiswa = m.id_mahasiswa " . $filter);

    while ($row = mysql_fetch_array($query, MYSQL_BOTH)) {
        $row_array['Id'] = $row['id_perkuliahan'];
        $row_array['Mhs']['Id'] = $row['id_mahasiswa'];
        $row_array['Mhs']['Nim'] = $row['nim'];
        $row_array['Mhs']['Nama'] = $row['nama'];
        $row_array['Mhs']['Alamat'] = $row['alamat'];
        $row_array['Mhs']['Telepon'] = $row['telepon'];
        $row_array['Mhs']['Email'] = $row['email'];
        $row_array['Mhs']['TahunAngkatan']['Id'] = $row['id_angkatan'];
        $row_array['Mhs']['TahunAngkatan']['Tahun'] = $row['tahun_angkatan'];

        array_push($array_matkul, $row_array);
    }
    return json_encode($array_matkul);
}

function viewPendingMember($matkul) {
    $array_matkul = array();
    $jsArray = json_decode($matkul, false);
    $query = mysql_query("SELECT * FROM tb_temp_perkuliahan p JOIN tb_mahasiswa m ON p.id_mahasiswa = m.id_mahasiswa
        WHERE id_det_matkul='" . $jsArray->{'Id'} . "'");

    while ($row = mysql_fetch_array($query, MYSQL_BOTH)) {
        $row_array['Id'] = $row['id_temp_perkuliahan'];
        $row_array['Mhs']['Id'] = $row['id_mahasiswa'];
        $row_array['Mhs']['Nim'] = $row['nim'];
        $row_array['Mhs']['Nama'] = $row['nama'];
        $row_array['Mhs']['Alamat'] = $row['alamat'];
        $row_array['Mhs']['Telepon'] = $row['telepon'];
        $row_array['Mhs']['Email'] = $row['email'];
        $row_array['Mhs']['TahunAngkatan']['Id'] = $row['id_angkatan'];
        $row_array['Mhs']['TahunAngkatan']['Tahun'] = $row['tahun_angkatan'];

        array_push($array_matkul, $row_array);
    }
    return json_encode($array_matkul);
}

function acceptPendingMemberByNim($json) {
    $query = mysql_query("INSERT INTO tb_perkuliahan(id_mahasiswa, id_det_matkul)
            SELECT t.id_mahasiswa, t.id_det_matkul FROM tb_temp_perkuliahan t 
            WHERE t.id_det_matkul='" . $json->{'DetMatkul'}->{'Id'} . "' AND t.id_mahasiswa='" . $json->{'Mhs'}->{'Id'} . "'");
    if ($query)
        $query = mysql_query("DELETE FROM tb_temp_perkuliahan WHERE id_det_matkul='" . $json->{'DetMatkul'}->{'Id'} . "' AND id_mahasiswa='" . $json->{'Mhs'}->{'Id'} . "'");
    if ($query)
        return true;
    return false;
}

function acceptPendingMember($json) {
    $query = mysql_query("INSERT INTO tb_perkuliahan(id_mahasiswa, id_det_matkul)
            SELECT t.id_mahasiswa, t.id_det_matkul FROM tb_temp_perkuliahan t 
            WHERE t.id_det_matkul='" . $json->{'DetMatkul'}->{'Id'} . "'  AND t.id_mahasiswa IN ('" . $json->{'Mhs'}->{'Id'} . "') ");
    if ($query)
        $query = mysql_query("DELETE FROM tb_temp_perkuliahan WHERE id_det_matkul='" . $json->{'DetMatkul'}->{'Id'} . "' AND id_mahasiswa IN ('" . $json->{'Mhs'}->{'Id'} . "')");
    if ($query)
        return true;
    else
        return false;
}

function rejectPendingMemberByNim($json) {
    $query = mysql_query("DELETE FROM tb_temp_perkuliahan WHERE id_det_matkul='" . $json->{'DetMatkul'}->{'Id'} . "' AND id_mahasiswa='" . $json->{'Mhs'}->{'Id'} . "'");
    if ($query)
        return true;
    return false;
}

function rejectPendingMember($json) {
    $query = mysql_query("DELETE FROM tb_temp_perkuliahan WHERE id_det_matkul='" . $json->{'DetMatkul'}->{'Id'} . "'");
    if ($query)
        return true;
    else
        return false;
}

function viewNilaiMember($perkuliahan) {
    $array_mhs = array();
    $jsArray = json_decode($perkuliahan, false);
    $query = mysql_query("SELECT * FROM tb_perkuliahan p 
        JOIN tb_mahasiswa m ON p.id_mahasiswa = m.id_mahasiswa
        JOIN tb_det_matkul d ON p.id_det_matkul=d.id_det_matkul
        WHERE p.id_det_matkul='" . $jsArray->{'DetMatkul'}->{'Id'} . "' AND p.id_mahasiswa='" . $jsArray->{'Mhs'}->{'Id'} . "'");

    while ($row = mysql_fetch_array($query, MYSQL_BOTH)) {
        $row_array['Id'] = $row['id_perkuliahan'];
        $row_array['Mhs']['Id'] = $row['id_mahasiswa'];
        $row_array['DetMatkul']['Id'] = $row['id_det_matkul'];
        $row_array['UTS'] = $row['nilai_uts'];
        $row_array['UAS'] = $row['nilai_uas'];
        $row_array['Tugas'] = $row['nilai_tugas_tot'];
        $row_array['Quiz'] = $row['nilai_quis'];

        array_push($array_mhs, $row_array);
    }
    return json_encode($array_mhs);
}

function updateNilaiMember($json) {
    $query = mysql_query("UPDATE tb_perkuliahan 
            SET nilai_uts=UPPER('" . $json->{'UTS'} . "'), 
            nilai_uas=UPPER('" . $json->{'UAS'} . "'), 
            nilai_tugas_tot=UPPER('" . $json->{'Tugas'} . "'), 
            nilai_quis=UPPER('" . $json->{'Quiz'} . "') 
            WHERE id_perkuliahan='" . $json->{'Id'} . "' OR
            (id_det_matkul='" . $json->{'DetMatkul'}->{'Id'} . "' AND id_mahasiswa='" . $json->{'Mhs'}->{'Id'} . "')");

    if ($query)
        return true;
    return false;
}

function importKuliah($json, $new) {

	foreach($json as $data){		
		$nim = $data['Nim'];
		$nip = $data['Nip'];
		$matkul = $data['Matkul'];
		$semester = $data['Semester'];
		$tahun = $data['TahunAjaran'];
		
		if($new){
			$queryId = mysql_query("SELECT COALESCE(max(id_temp_perkuliahan), 0) + 1 FROM tb_det_matkul");
			$detMatkulId = mysql_fetch_array($queryId, MYSQL_BOTH);
			
			$query = mysql_query("INSERT INTO tb_det_matkul VALUES " .
				"(" . $detMatkulId . ", " . $nip . ", " . $matkul . ", '" . $semester . "', '" . $tahun . "')");
			
			if($query)
				$query2 = mysql_query("INSERT INTO tb_perkuliahan(id_mahasiswa, id_det_matkul) VALUES (" . $nim . ", " . $matkul . ")");
					
			if (!$query || !$query2){
				break;
				return false;
			}		
		} else {			
			$count = mysql_num_rows(mysql_query("SELECT id_mahasiswa FROM tb_perkuliahan WHERE id_mahasiswa='" . $nim . "' and id_det_matkul='" . $matkul . "'"));
			if ($count < 1) {
				$query2 = mysql_query("INSERT INTO tb_perkuliahan(id_mahasiswa, id_det_matkul) VALUES (" . $nim . ", " . $matkul . ")");
						
				if (!$query2){
					break;
					return false;
				}		
			}
		}
	}
    return true;
}
?>
